Apache SupersetでAmazon Athenaのクエリ結果をビジュアライズする

Apache SupersetでAmazon Athenaのクエリ結果をビジュアライズする

データ分析

データ分析

#re:dash
#Amazon Athena
#AWS
八幡豊

八幡豊

facebook logohatena logotwitter logo
Clock Icon2017.12.27

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

以前RedashでAmazon Athenaのクエリ結果をビジュアライズする方法をご紹介しました。

今回は同じくOSSのBIツールであるApache Superset（以降Superset）を使ってAmazon Athenaのクエリ結果をビジュアライズする方法をご紹介します。

目次

Supersetとは

Airbnb社が2016年にリリースしたOSSのBIツール（BI Webアプリケーション）です。現在はApache Incubatorプロジェクトとして開発が行われています。

全体的な使い勝手やできることはRedashと類似する点が多いですが、GUI操作のみで（クエリを書くことなく）データをビジュアライズできるところがSupersetの特徴の一つかと思います（具体的なビジュアライズ手順は後述します）。

Supersetのセットアップ（Docker-Composeの設定）

今回は手元のmacOS上で、Dockerを使ってSupersetの環境をセットアップする手順をご紹介します。

SupersetはPython製のアプリケーションフレームワークFlaskで作られています。pip install supersetでインストールすることがでるため、Docker以外の環境でも導入は比較的容易かと思います。

詳しいインストール手順は公式ドキュメントを参照ください。

動作確認環境

  • OS : macOS High Sierra 10.13.2
  • Docker : Community Edition 17.09.1
  • Superset : v0.22.1

Dockerイメージ

現時点ではSuperset公式のDockerイメージは提供されていません。代わりにSupersetのGithubリポジトリでCommunity contributedのDockerイメージとして紹介されているamancevice/supersetを利用します。

SupersetからAmazon Athenaに接続するためにはPyAthenaJDBC（Amazon AthenaのJDBCドライバーのラッパーライブラリ）が必要です。上記のDockerイメージにはこのライブラリが含まれています。

Docker Composeの設定

「amancevice/superset」のGithubリポジトリにサンプルのdocker-compose.ymlがあるのでこれを利用します（一部変更を加えます）。

サンプルはMySQL, PostgreSQL, SQLite、Celeryの4つが用意されています。それぞれ構成は以下の通りです。

サンプル Webサーバー メタデータDB ワーカー メッセージキュー クエリ結果の保存先
Celery Gunicorn PostgreSQL 有り Redis Redis
MySQL Gunicorn MySQL 無し - -
PostgreSQL Gunicorn PostgreSQL 無し - -
SQLite Gunicorn SQLite 無し - -

お試し環境であればどのサンプルを使っても問題ないかと思います。実運用環境では、数十分〜数時間の長時間のクエリを実行するケースも想定し、ワーカーを使って非同期にクエリを処理する構成にするのがよいかと思います。

ちなみにメッセージキューにはRabbitMQやAmazon SQS、クエリ結果の保存先にはAmazon S3やMemcachedも使えるようなので、別の機会に試してみたいと思います。

ディレクトリ構成

.
├── docker-compose.yml
├── postgres
├── redis
└── superset
    └── superset_config.py
  • 「postgres」、「redis」、「superset」ディレクトリを用意しコンテナからマウントします。
  • 「postgres」、「redis」にはそれぞれのDBファイルを格納します。
  • 「superset」にはSupersetの設定フィルsuperset_config.pyを置きます（Supersetの起動時に「/etc/superset/superset_config.py」が自動的に読み込まれます）。

docker-compose.yml

version: '3'
services:
  redis:
    image: redis
    restart: always
    volumes:
      - ./redis:/data
  postgres:
    image: postgres
    restart: always
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_USER: ${POSTGRES_USER}
    volumes:
      - ./postgres:/var/lib/postgresql/data
  superset:
    image: amancevice/superset
    restart: always
    depends_on:
      - postgres
      - redis
    environment:
      MAPBOX_API_KEY: ${MAPBOX_API_KEY}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      SECRET_KEY: ${SECRET_KEY}
    ports:
      - "8088:8088"
    volumes:
      - ./superset:/etc/superset
  worker:
    image: amancevice/superset
    restart: always
    depends_on:
      - postgres
      - redis
    environment:
      MAPBOX_API_KEY: ${MAPBOX_API_KEY}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
      SECRET_KEY: ${SECRET_KEY}
    volumes:
      - ./superset:/etc/superset
    command: worker
    healthcheck:
      disable: true
volumes:
  postgres:
    external: false
  redis:
    external: false
  • Redis、PostgreSQL、Superset、Superset workerの4コンテナを起動します。
  • 以下サンプルからの変更点です。
    • 以下の値を環境変数に設定
      • PosgreSQLのユーザー名、パスワード、DB名
      • Supersetのシークレットキー（セッション情報を暗号化するために利用）
    • workerコンテナのヘルスチェックを無効化
      • 「amancevice/superset」のDockerイメージにはヘルスチェックが設定されています（HEALTHCHECK CMD ["curl", "-f", "http://localhost:8088/health"]）
      • workerではこのヘルスチェックが失敗するため、あらかじめ無効化しておきます。

.envファイル

POSTGRES_USER=superset
POSTGRES_PASSWORD=password
POSTGRES_DB=superset
MAPBOX_API_KEY=mapboxapikey
SECRET_KEY=veryverysecret
  • 値はサンプルです。各環境に合わせて適当な値に変更してください。
  • SupersetはMapboxを使ってマップを表示することができます。MapboxのAPIキーを取得しMAPBOX_API_KEYにセットします。

superset_config.pyファイル

import os

from werkzeug.contrib.cache import RedisCache

MAPBOX_API_KEY = os.getenv('MAPBOX_API_KEY', '')
postgres_server = 'postgres'
postgres_user = os.getenv('POSTGRES_USER', '')
postgres_password = os.getenv('POSTGRES_PASSWORD', '')
postgres_db = os.getenv('POSTGRES_DB', '')
CACHE_CONFIG = {
    'CACHE_TYPE': 'redis',
    'CACHE_DEFAULT_TIMEOUT': 300,
    'CACHE_KEY_PREFIX': 'superset_',
    'CACHE_REDIS_HOST': 'redis',
    'CACHE_REDIS_PORT': 6379,
    'CACHE_REDIS_DB': 1,
    'CACHE_REDIS_URL': 'redis://redis:6379/1'}
SQLALCHEMY_DATABASE_URI = \
    'postgresql+psycopg2://%s:%s@%s:5432/%s' % (postgres_user, postgres_password, postgres_server, postgres_db)
SQLALCHEMY_TRACK_MODIFICATIONS = True
SECRET_KEY = os.getenv('SECRET_KEY', '')


class CeleryConfig(object):
    BROKER_URL = 'redis://redis:6379/0'
    CELERY_IMPORTS = ('superset.sql_lab', )
    CELERY_RESULT_BACKEND = 'redis://redis:6379/0'
    CELERY_ANNOTATIONS = {'tasks.add': {'rate_limit': '10/s'}}


CELERY_CONFIG = CeleryConfig
RESULTS_BACKEND = RedisCache(
    host='redis',
    port=6379,
    key_prefix='superset_results'
)
  • 以下の値は環境変数から取得する形に変更しています。
    • PosgreSQLのユーザー名、パスワード、DB名
    • Supersetのシークレットキー（セッション情報を暗号化するために利用）

AWSで運用する場合、秘匿情報はSystems Manager パラメータストアを使って管理する方法も考えられます。こちらも別の機会に試してみたいと思います。

Supersetのセットアップ（起動〜初期設定）

コンテナ起動

  • 準備が整ったら、RedisとPostgreSQL → Superset → Superset workerの順番でコンテナを起動します。
# RedisとPostgresqlを起動
$ docker-compose up -d redis postgres

# Supersetを起動
$ docker-compose up -d superset

# workerを起動
$ docker-compose up -d worker

初期設定

  • 以下を実行します。
    • 管理者ユーザーの作成
    • データベースの初期化
    • デモデータのロード（必要に応じて実行）
    • ロールとパーミッションの作成
# 管理者ユーザー作成
$ docker-compose exec superset fabmanager create-admin --app superset
Username [admin]: 
User first name [admin]: 
User last name [user]: 
Email [[email protected]]: [email protected]
Password: 
Repeat for confirmation: 

# データベースの初期化
$ docker-compose exec superset superset db upgrade

# デモデータのロード（必要に応じて実行）
$ docker-compose exec superset superset load_examples

# ロールとパーミッションの作成
# Supersetではロールとパーミッションを利用してユーザーの権限を管理する（ロールを作成＆パーミッションを紐つける）
$ docker-compose exec superset superset init

ちなみに「amancevice/superset」のDockerイメージにはSupersetの初期設定用のラッパースクリプトが含まれています。これを利用すると上記の初期設定一式をシングルコマンドで実行できます。

# デモデータのロードなし
$ docker-compose exec superset superset-init

# デモデータをロードあり（superset-demoスクリプト内でsuperset-init＆データロードが実行される）
$ docker-compose exec superset superset-demo

Webサーバーを起動

初期設定が終わればSupersetが利用できる状態になります。Webサーバーを起動してログインしてみます。

$ docker-compose exec superset superset runserver
  • UIを日本語することができます（ただし日本語化されるのはメニューバーの一部のみ、という感じです）。

  • 管理者ユーザーでログインします。

  • ダッシュボードの一覧が表示されます（※デモデータをロードした場合）

SupersetからAmazon Athenaにクエリを実行

試しにAWSの公式ドキュメントにあるAmazon AthenaのGetting Startedに倣って、 SupersetからAmazon Athenaにクエリを実行しCloudFrontのサンプルログを分析してみます。

IAMユーザー作成

はじめに、Amazon AthenaとS3へのアクセス許可を持ったIAMユーザーを作成します。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "athena:BatchGet*",
                "athena:Get*",
                "athena:List*",
                "athena:StartQueryExecution",
                "athena:StopQueryExecution"
            ],
            "Resource": [
                "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload",
                "s3:CreateBucket",
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::aws-athena-query-results-*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::athena-examples-ap-northeast-1"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::athena-examples-ap-northeast-1/cloudfront/plaintext/*"
            ]
        }
    ]
}

aws-athena-query-results-*はAmazon Athenaがクエリ結果を保存するS3バケットです（デフォルトでは「s3://aws-athena-query-results-<AWSアカウントID>-＜リージョン>/」）。このバケットに対して適当な権限を付与します。

クエリ結果の保存先S3バケットの設定はAmazozn Athenaのマネージメントコンソールの「Settings」から確認できます。

Amazon Athenaのテーブル作成

Amazon Athenaのマネージメントコンソールを開いてAmazon Athenaのテーブルを作成します。

  • データベースを作成します。
create database superset_sampledb;

  • テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS superset_sampledb.cloudfront_logs (
  `Date` DATE,
  Time STRING,
  Location STRING,
  Bytes INT,
  RequestIP STRING,
  Method STRING,
  Host STRING,
  Uri STRING,
  Status INT,
  Referrer STRING,
  os STRING,
  Browser STRING,
  BrowserVersion STRING
  ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
  "input.regex" = "^(?!#)([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+([^ ]+)\\s+[^\(]+[\(]([^\;]+).*\%20([^\/]+)[\/](.*)$"
  ) LOCATION 's3://athena-examples-ap-northeast-1/cloudfront/plaintext/';

データソースの登録

Supersetの画面に戻り、データソースにAmazon Athenaを登録します。

  • 「ソース」-「データベース」をクリックします。

  • 「追加」ボタンをクリックします。

  • Database名（任意の名称）を入力します。
  • 「SQLAlchemy URI」は以下のように入力します。
awsathena+jdbc://{aws_access_key_id}:{aws_secret_access_key}@athena.{region_name}.amazonaws.com/{schema_name}?s3_staging_dir={s3_staging_dir}&...

（例）

awsathena+jdbc://AKXXXXXXXXXX:[email protected]/superset_sampledb?s3_staging_dir=s3%3A//aws-athena-query-results-111111111111-ap-northeast-1/

  • 「Test Connection」を実行し「Seems OK!」と表示されることを確認します。

  • 「Expose in SQL Lab」にチェックを入れます。
    • 「SQL Lab」（日本語では「SQLツールと表示されます）はSQLエディタです。
    • SQLエディタからクエリを実行するためには「Expose in SQL Lab」にチェックを入れる必要があります。
  • 「保存」ボタンをクリックして設定を保存します。

  • ソースの一覧に登録したデータソースが表示されることを確認します。

クエリの実行

  • 「SQLツール」-「SQLエディタ」をクリックします。

  • 左ペインの「Database」で先ほど登録したAmazon Athenaを選択します。
  • その下の「Schema」にはAmazon Athena上に作成済みのデータベースの一覧が表示されます。一覧から先ほど作成した「superset_sampledb」を選択します。

  • 「Schema」の下の「Add a table」からテーブルを選択するとカラムの一覧が表示されます。
  • さらに右ペインに「Preview for ＜テーブル名＞」タブが追加され、テーブルのレコードが100件（Select * from ... LIMIT 100）表示されます。

  • 以下のクエリを実行し、OS別のリクエスト数を集計してみます。
SELECT os,
       COUNT(*) COUNT
FROM superset_sampledb.cloudfront_logs
WHERE date BETWEEN date '2014-07-05' AND date '2014-08-05'
GROUP BY os;

  • 「Results」にクエリ結果が表示されます。

クエリの実行結果のビジュアライズ

クエリの実行結果をビジュアライズすることができます。

  • 「Results」に表示される「Visualize」ボタンをクリックします。

  • 「Chart Type」を選択します（画面では「Pie Chart」を選択しています）。
  • 「Datasouce Name」にはデータソース名が「＜ユーザー名＞-＜DB名＞-＜クエリ名＞〜」という書式で自動的に入力されます。必要に応じてデータソース名を変更します。
  • 「Visualize」をクリックします。

  • チャートが表示されます。

  • 「Save」をクリックして作成したパイチャートを保存します。

  • 「Save A Slice」の画面が表示されます（Supersetでは一つ一つのビューを「スライス」と呼びます）。
  • 「スライス名」を入力します。
  • 複数のスライスをまとめてダッシュボードに表示することができます。「Add to new dashboard」を選択するとスライスの作成と同時にダッシュボードも新規作成できます。
  • 「Save & go to dashborad」をクリックします。

  • ダッシュボードが表示されます。

GUI操作でのビジュアライズ

SQLエディタでクエリを書かずに、GUI操作でスライスを作成することも可能です。

テーブルの登録

スライスを作成するためにはテーブルをデータソースに登録する必要があります。

  • 「ソース」-「テーブル」を選択します。

  • 「CloudFront Logs」というデータソースが表示されます。これは先ほど実行したSQLエディタからクエリ実行＆ビジュアライズした過程で作成されたものです。

  • 新規にデータソースを登録します。「追加」ボタンをクリックします。

  • 「Database」でAmazon Athenaを選択します。
  • 「スキーマ（データベース名）」と「テーブル名」を入力し「保存」をクリックします（※テーブル登録の画面ではスキーマとテーブル名は一覧から選択できません。手入力する必要があります）。

  • テーブル一覧に追加したテーブルが表示されることを確認します。
  • データソース名（テーブル名）は「＜スキーマ名＞.＜テーブル名＞」という形式で自動的に設定されます（今のところ変更はできないようです）。

スライスの作成

GUI操作でスライスを作成します。

  • 「スライス」をクリックします。

  • 「追加」ボタンをクリックします。

  • 先ほど追加したデータソース（テーブル）を選択します。
  • ビジュアライズの種類を選択します。
  • 「Create new slice」をクリックします。

  • スライスの作成画面が表示されます。

  • CloudFrontのロケーション別のアクセス数をWord Cloudで表示してみます。
  • 「Time」で期間を指定します。「Since」をクリックして期間の「開始」を設定します。

  • 期間の開始日時を入力して「OK」をクリックします（カレンダーから日時選択することや相対日時を指定することもできます）。

  • 同様に「Until」をクリックし期間の「終了」を設定します。

  • 「Query」で以下を選択します。
    • Series：location
    • Metrics：COUNT(*)（※集計関数を選択します。選択できるものはCOUNT(*)とAVGとSUMのみです）

  • 「Options」でフォントサイズなどを設定できます。

  • 「SQL」でWHERE句、HAVING句を追加できます。

  • 「Filters」でデータをフィルタリングできます（WHERE句での絞り込みです。「SQL」で手動でWHERE句を追加するのと同じ結果が得られます。インタラクティブフィルターが追加されるわけではありません）。

  • 「Run Query」を実行するとクエリ結果がビジュアライズされます。

  • ダッシュボードに追加してみます。

  • 「Add slice to existing dashboard」を選択し、既存のダッシュボードにスライスを追加します。

  • ダッシュボードにスライスが追加されました。

  • ダッシュボードを保存します。

まとめ

Tableauなどの商用BIツールと比較するとさすがにできることは限られますが、ダッシュボードツールとしては非常にシンプルで使い易い印象を持ちました。 GUI操作のみでデータをビジュアライズ出来るのは便利ですが、現時点ではデータの集計方法が限られるので、実際の運用ではSQLを書いてビジュアライズというワークフローがメインになりそうです。

今後もRedashと合わせて使い込んでいきたいと思います。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

Docker Desktop for Apple siliconでRedashを動作させてみた

Docker Desktop for Apple siliconでRedashを動作させてみた

User avatar
haoyayoi
2021.11.02
getredash/redashがローカル環境でError code 139と出て起動しなくなっていたので取り敢えず暫定復旧させてみた

getredash/redashがローカル環境でError code 139と出て起動しなくなっていたので取り敢えず暫定復旧させてみた

User avatar
haoyayoi
2021.10.01
Redashに環境変数での設定変更が反映されていることをgunicornで確認してみた

Redashに環境変数での設定変更が反映されていることをgunicornで確認してみた

User avatar
haoyayoi
2021.07.13
RedashでScheduler更新時に発生するエラー「There’s already an active RQ scheduler」について原因を調べて解消してみた

RedashでScheduler更新時に発生するエラー「There’s already an active RQ scheduler」について原因を調べて解消してみた

User avatar
haoyayoi
2021.07.09
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.